Using LOD Expressions with Filters in Tableau

Topic: Using Level of Detail (LOD) Expressions with Filters

Objective: Learn how Tableau filters affect FIXED, INCLUDE, and EXCLUDE LOD expressions, understand Tableau's order of operations, and build calculations that respond correctly to interactive filters.

Course: JustAcademy Tableau Training

Course Demo: Register for a Tableau Course Demo

1. Introduction to LOD Expressions with Filters

LOD stands for Level of Detail. LOD expressions allow you to calculate measures at a specified level of detail, even when the visualization displays data at a different level.

Filters control which records, values, or marks are included in a Tableau analysis. When filters are combined with LOD expressions, the order in which Tableau evaluates them determines whether a filter changes the calculation.

For example, imagine a sales dashboard that displays sales by region. A user selects only the East region. Depending on the calculation and filter type, Tableau may calculate the total using only East-region records or retain the total sales for all regions while hiding the other regions from the chart.

Why Is This Important?

• Understand why some LOD calculations appear unaffected by filters.
• Create accurate percentage-of-total calculations.
• Build dashboards with interactive region, category, customer, and date filters.
• Calculate totals using either all relevant records or only filtered records.
• Compare selected data against an overall benchmark.
• Prevent misleading results caused by an incorrect filter order.

2. Understanding Tableau's Order of Operations

Tableau evaluates different filters and calculations in a defined order. This order is important because a filter only affects a calculation when it is applied at the appropriate stage.

Simplified Order of Operations

1. Extract filters
2. Data source filters
3. Context filters
4. FIXED LOD expressions
5. Dimension filters
6. INCLUDE and EXCLUDE LOD expressions
7. Measure filters
8. Table calculation filters

This is a simplified view of the relevant order of operations, not the complete sequence of every Tableau operation.

Key rule: FIXED LOD expressions are evaluated before ordinary dimension filters. INCLUDE and EXCLUDE LOD expressions are evaluated after dimension filters. Read Tableau's official guide to filters and LOD expressions.

3. FIXED LOD Expressions and Filters

A FIXED expression calculates a value using the dimensions explicitly specified in the expression. It does not use the dimensions in the view to determine its calculation granularity.

Syntax

{FIXED [Dimension] : AGGREGATION([Measure])}

Example: Total Sales Across All Regions

{FIXED : SUM([Sales])}

This expression calculates total sales for the data domain available to the calculation. Ordinary dimension filters in the view do not normally change this total, although extract, data source, and context filters can affect it.

Example: Total Sales by Region

{FIXED [Region] : SUM([Sales])}

This expression calculates a sales total for each region. If the visualization also contains Product Name, the regional total may repeat for each product in that region.

What Happens When You Apply a Dimension Filter?

Suppose total sales are calculated as follows:

{FIXED : SUM([Sales])}

You add Region to the Filters shelf and select East. The chart now displays only East-region marks, but the FIXED total can still include sales from East, West, Central, and other regions that remain in the calculation's data domain.

This happens because the ordinary Region dimension filter is evaluated after the FIXED expression.

When Should You Use FIXED with Filters?

• When the denominator must remain the overall total even when some dimension values are hidden.
• When comparing filtered results against a company-wide benchmark.
• When you need a stable customer-level or region-level calculation.
• When a context filter should restrict the records included in the FIXED calculation.

4. Using Context Filters with FIXED LOD Expressions

A context filter is evaluated before a FIXED LOD expression. Converting an ordinary dimension filter to a context filter allows the filter to restrict the data used by the FIXED calculation.

Example

{FIXED : SUM([Sales])}

Suppose you filter Region to East.

• Ordinary dimension filter: The FIXED total can still include sales from all regions.
• Context filter: The FIXED calculation is evaluated using the data remaining after the Region context filter, so the total can reflect East only.

How to Create a Context Filter

1. Open a Tableau worksheet.
2. Drag Region to the Filters shelf.
3. Select the required region or regions.
4. Confirm the filter selection.
5. Right-click the Region pill on the Filters shelf.
6. Select Add to Context.
7. Observe how the FIXED calculation changes.

In Tableau, a context filter is typically displayed in gray on the Filters shelf.

Important Considerations

• Use context filters when the calculation genuinely needs to operate on the filtered data domain.
• Do not convert every filter to context automatically; decide based on the intended calculation.
• Check whether multiple filters must jointly restrict the FIXED calculation.
• Context filters can change the business meaning of totals and percentages.

5. INCLUDE LOD Expressions and Filters

An INCLUDE expression adds specified dimensions to the dimensions already present in the view. INCLUDE calculations are evaluated after dimension filters, so dimension filters can affect the data used in the calculation.

Syntax

{INCLUDE [Dimension] : AGGREGATION([Measure])}

Example: Customer Sales

{INCLUDE [Customer Name] : SUM([Sales])}

If Region is displayed in the view, this expression calculates sales at customer-level detail in the context of the view. Using AVG on the resulting customer-level values can help compare average customer sales by region.

What Happens When You Apply a Region Filter?

1. Create the INCLUDE calculation for customer sales.
2. Add Region to the view.
3. Add Region to the Filters shelf.
4. Select East.
5. Compare the results before and after applying the filter.

Because INCLUDE is evaluated after dimension filters, the Region filter can affect the records used by the calculation.

When to Use INCLUDE with Filters

• When the calculation should respect dimension filters.
• When you need customer-level or order-level detail inside a broader view.
• When comparing filtered groups using an average of detailed results.

6. EXCLUDE LOD Expressions and Filters

An EXCLUDE expression removes one or more dimensions from the view's level of detail for the calculation. EXCLUDE calculations are evaluated after dimension filters.

Syntax

{EXCLUDE [Dimension] : AGGREGATION([Measure])}

Example: Sales Without Category Detail

{EXCLUDE [Category] : SUM([Sales])}

If Category is present in the view, this expression removes Category from the calculation's view-level detail. It can be used to compare category-level marks with a broader total.

How Filters Affect EXCLUDE

Suppose Category is displayed and a Region dimension filter is applied. Since EXCLUDE is evaluated after dimension filters, the Region filter can affect the data used in the calculation. However, excluding Category from the LOD calculation does not automatically remove the effects of every other dimension or filter.

When to Use EXCLUDE with Filters

• When a calculation needs to remove a dimension from the current view context.
• When comparing category sales with totals at a broader level.
• When calculating differences from a filtered average or total.
• When creating percent-of-total calculations that depend on the dimensions in the view.

7. Comparing FIXED, INCLUDE, and EXCLUDE with Filters

Choose the expression based on the required calculation scope and filter behavior, rather than assuming that one type is always better than another.

8. Example Dataset for Understanding Filter Behavior

Consider the following sample data:

Scenario A: Ordinary Region Filter

Create the calculation:

{FIXED : SUM([Sales])}

Apply an ordinary Region filter and select East.

• Visible East sales = 1000 + 1500 = 2500.
• Overall FIXED total can remain 6000.
• East's share of the overall total = 2500 / 6000 = 41.67%.

Scenario B: Region as a Context Filter

Convert Region to a context filter and select East.

• Visible East sales = 2500.
• FIXED total after the context filter = 2500.
• East's share of the filtered total = 2500 / 2500 = 100%.

These results demonstrate how filter order can change the meaning of a percentage calculation.

9. Calculating Percentage of Total with LOD and Filters

A percentage-of-total calculation divides the selected measure by a broader total. The LOD expression determines which data contributes to the denominator.

Formula: Percentage of Overall Sales

SUM([Sales]) / ATTR({FIXED : SUM([Sales])})

Format the result as a percentage. With an ordinary Region filter, the denominator can include all regions, including those hidden from the view.

Formula: Percentage of Sales in the Filtered Context

Use the same formula:

SUM([Sales]) / ATTR({FIXED : SUM([Sales])})

Then add the relevant dimension filter to context. The FIXED denominator can now reflect the filtered data domain.

Choosing the Correct Formula Behavior

Important: The last option is not a universal replacement for FIXED. INCLUDE and EXCLUDE use the dimensions in the view, so their results can change when the view changes.

10. Using LOD Expressions as Filters

LOD expressions can also be used to create calculated fields that can be placed on the Filters shelf. This allows you to filter data based on a value calculated at a different level of detail.

Example: Filter Customers by Total Sales

Create a calculated field named Customer Total Sales:

{FIXED [Customer ID] : SUM([Sales])}

Drag Customer Total Sales to the Filters shelf and select a suitable range, such as customers with total sales above a business-defined threshold.

Example: Identify Customers' First Purchase Date

{FIXED [Customer ID] : MIN([Order Date])}

This calculation returns the earliest order date for each customer within the calculation's data domain. It can help create customer cohorts or identify customers whose first purchase occurred in a specified period.

Important Considerations

• Check whether the LOD field is treated as a dimension or a measure in the Data pane.
• Use a filter control appropriate to the field's data type.
• Remember that filtering on an LOD field does not change the LOD's position in Tableau's order of operations.
• Test the result when other dimension filters are applied.

11. Step-by-Step Practical Exercise

Project: Build a regional sales dashboard with an overall total and a filtered total.

Step 1: Connect to a Dataset

Open Tableau and connect to a sales dataset containing Region, Category, Customer ID, Order ID, Order Date, and Sales.

Step 2: Create the Overall Sales Calculation

{FIXED : SUM([Sales])}

Name the calculated field Overall Sales.

Step 3: Create Regional Sales

{FIXED [Region] : SUM([Sales])}

Name the field Regional Sales.

Step 4: Create Percentage of Overall Sales

SUM([Sales]) / ATTR([Overall Sales])

Name the field Percentage of Overall Sales and format it as a percentage.

Step 5: Build the Visualization

1. Drag Region to Rows.
2. Drag SUM(Sales) to Columns.
3. Add Percentage of Overall Sales to Label or Tooltip.
4. Drag Region to the Filters shelf.
5. Display the filter so users can change the selected region.

Step 6: Test an Ordinary Dimension Filter

Select only East. Record the visible sales and percentage. Confirm that the denominator can remain the overall total.

Step 7: Test a Context Filter

Right-click the Region pill on the Filters shelf and select Add to Context. Observe how the FIXED overall total and percentage change.

Step 8: Add a Category Filter

Add Category to Filters and test it as an ordinary dimension filter. Compare the result with the behavior when Category is added to context. Record the differences.

Step 9: Create an INCLUDE Calculation

{INCLUDE [Customer ID] : SUM([Sales])}

Use AVG on the resulting customer-level sales values in a regional view to analyze average customer sales. Test the calculation before and after filtering Region.

Step 10: Create an EXCLUDE Calculation

{EXCLUDE [Category] : SUM([Sales])}

Build a view containing Region and Category, then compare category-level sales with the calculation that excludes Category. Apply a Region filter and observe the result.

12. Common Problems and Solutions

13. Best Practices

• Define whether a total should represent all data or only filtered data before creating the calculation.
• Understand Tableau's order of operations before troubleshooting an LOD expression.
• Use context filters deliberately when FIXED calculations need to respect selected filters.
• Do not assume that INCLUDE and EXCLUDE are interchangeable with FIXED.
• Test calculations with a small text table before adding them to a dashboard.
• Check the impact of filters on both the numerator and denominator of percentage calculations.
• Use descriptive field names such as Overall Sales, Filtered Sales, and Customer Total Sales.
• Validate LOD calculations with manual calculations or a trusted summary.
• Document whether each dashboard percentage uses an overall total or a filtered total.
• Test dashboard filters together, because multiple filters may interact.

14. Practice Questions

1. Why might a FIXED LOD calculation ignore an ordinary dimension filter?
2. What is a context filter, and how does it affect FIXED expressions?
3. How do INCLUDE and EXCLUDE differ from FIXED in filter order?
4. Write an expression to calculate total sales across the entire available data domain.
5. Write an expression to calculate sales by region.
6. How would you calculate each customer's total sales?
7. Why might a percentage-of-total calculation retain a denominator that includes hidden regions?
8. How can you calculate a customer's first purchase date?
9. What should you check if an EXCLUDE value appears repeated in multiple marks?
10. How would you test whether a filter affects the expected LOD calculation?

Sample Answers

1. FIXED is evaluated before ordinary dimension filters.
2. A context filter is evaluated before FIXED and restricts the data domain used by the FIXED calculation.
3. INCLUDE and EXCLUDE are evaluated after dimension filters, while FIXED is evaluated before ordinary dimension filters.
4. {FIXED : SUM([Sales])}
5. {FIXED [Region] : SUM([Sales])}
6. {FIXED [Customer ID] : SUM([Sales])}
7. The denominator may be a FIXED total calculated before the ordinary dimension filter is applied.
8. {FIXED [Customer ID] : MIN([Order Date])}
9. Check the dimensions in the view and the dimension excluded from the expression.
10. Compare the results with and without the filter, and test the filter as a context filter if relevant.

15. Interview Questions and Answers

Q1. What happens when you apply a dimension filter to a FIXED LOD expression?

Answer: The ordinary dimension filter is evaluated after FIXED. Therefore, the filter generally does not change the FIXED result, although it can hide marks from the view.

Q2. How can a FIXED expression be made to respect a dimension filter?

Answer: If appropriate for the analysis, convert the dimension filter to a context filter. Context filters are evaluated before FIXED LOD calculations.

Q3. Do INCLUDE and EXCLUDE LOD expressions respond to dimension filters?

Answer: They are evaluated after dimension filters, so those filters can affect their calculations.

Q4. What is the difference between an ordinary dimension filter and a context filter?

Answer: An ordinary dimension filter is evaluated after FIXED LOD calculations, while a context filter is evaluated before them and can restrict the data used by FIXED.

Q5. Why can a percentage-of-total value change when a filter is added to context?

Answer: The FIXED denominator may be recalculated using the filtered data domain, changing the total against which the numerator is compared.

Q6. Can LOD expressions be used as filters?

Answer: Yes. An LOD calculated field can be placed on the Filters shelf to filter according to a value calculated at a specified level of detail.

Q7. Why does a FIXED total sometimes remain the same across different views?

Answer: FIXED uses the dimensions specified in the expression rather than the dimensions in the view. Its result can still be affected by extract, data source, and context filters.

Q8. When should you use a context filter?

Answer: Use one when the filtered data domain needs to be established before a FIXED calculation. It should be used based on the intended analytical behavior, not automatically for every filter.

Q9. What is a common cause of incorrect LOD percentages?

Answer: The numerator and denominator may represent different data domains, especially when ordinary dimension filters affect the numerator but not a FIXED denominator.

Q10. What is the first thing to check when an LOD result seems incorrect?

Answer: Check the calculation's granularity, the dimensions in the view, the filter types, and Tableau's order of operations.

16. Quick Revision Checklist

• Understand the order of operations for filters and LOD calculations.
• Remember that FIXED is evaluated before ordinary dimension filters.
• Know that context filters can affect FIXED calculations.
• Remember that INCLUDE and EXCLUDE are evaluated after dimension filters.
• Understand why a FIXED denominator may include data hidden from the view.
• Know how to compare overall totals with filtered totals.
• Test filter behavior in a worksheet before building a dashboard.
• Check the numerator and denominator when calculating percentages.
• Document whether dashboard metrics should use all data or only filtered data.

17. Conclusion

Using LOD expressions with filters is an important Tableau skill because filter order determines which records contribute to a calculation. FIXED expressions calculate at explicitly declared dimensions and are evaluated before ordinary dimension filters. Context filters can restrict the data used by FIXED calculations. INCLUDE and EXCLUDE expressions are evaluated after dimension filters and use the dimensions in the view to establish their calculation context.

By understanding these differences, you can build accurate dashboards, calculate meaningful percentages, compare filtered results with overall totals, and troubleshoot unexpected values in Tableau.

Learn more: JustAcademy Tableau Training | Register for a Tableau Course Demo

Official Tableau References

• Filters and Level of Detail Expressions
• How Level of Detail Expressions Work in Tableau
• Create Level of Detail Expressions in Tableau
• Understanding LOD Expressions